package com.zxy.ziems.server.statistics.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.thoughtworks.xstream.core.BaseException;
import com.zxy.btp.common.exception.ConditionException;
import com.zxy.ziems.common.pojo.MeasureDataDayBean;
import com.zxy.ziems.server.classify.pojo.entity.Classify;
import com.zxy.ziems.server.classify.pojo.vo.ClassifyVO;
import com.zxy.ziems.server.classify.service.ClassifyService;
import com.zxy.ziems.server.device.mapper.DeviceMapper;
import com.zxy.ziems.server.device.mapper.DevicePropertyMapper;
import com.zxy.ziems.server.device.pojo.entity.DeviceEntity;
import com.zxy.ziems.server.energy.mapper.DeviceMeasureDataDayMapper;
import com.zxy.ziems.server.energy.mapper.DeviceMeasureDataHistoryMapper;
import com.zxy.ziems.server.energy.pojo.entity.DeviceMeasureDataDayEntity;
import com.zxy.ziems.server.energy.service.EnergyPriceService;
import com.zxy.ziems.server.mapper.ExhausGasChemicalMapper;
import com.zxy.ziems.server.mapper.ExhausGasChemicalRelMapper;
import com.zxy.ziems.server.service.DictItemService;
import com.zxy.ziems.server.statistics.pojo.dto.ExhaustGasChemicalsDto;
import com.zxy.ziems.server.statistics.pojo.entity.ExhaustGasChemicalsData;
import com.zxy.ziems.server.statistics.pojo.entity.ExhaustGasChemicalsRelData;
import com.zxy.ziems.server.statistics.pojo.entity.GasConsumptionData;
import com.zxy.ziems.server.statistics.pojo.entity.GasConsumptionRelData;
import com.zxy.ziems.server.statistics.pojo.param.ExhausGasChemicalQueryParam;
import com.zxy.ziems.server.statistics.pojo.vo.ExhaustGasChemicalsVo;
import com.zxy.ziems.server.statistics.pojo.vo.ValueLabelVo;
import com.zxy.ziems.server.statistics.service.ExhausGasChemicalService;
import com.zxy.ziems.server.storage.service.MeasureDataDayStorageService;
import com.zxy.ziems.server.utils.IdUtil;
import com.zxy.ziems.server.utils.StringUtils;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.record.DVALRecord;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.swing.plaf.synth.Region;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service
public class ExhausGasChemicalServiceImpl implements ExhausGasChemicalService {

    @Autowired
    private DictItemService dictItemService;
    @Autowired
    private ClassifyService classifyService;

    @Autowired
    private ExhausGasChemicalMapper exhausGasChemicalMapper;

    @Autowired
    private ExhausGasChemicalRelMapper exhausGasChemicalRelMapper;

    @Autowired
    private DevicePropertyMapper devicePropertyMapper;
    @Autowired
    private DeviceMapper deviceMapper;
    @Autowired
    private MeasureDataDayStorageService measureDataDayStorageService;
    @Autowired
    private DeviceMeasureDataDayMapper deviceMeasureDataDayMapper;
    @Autowired
    private DeviceMeasureDataHistoryMapper deviceMeasureDataHistoryMapper;
    @Autowired
    private EnergyPriceService energyPriceService;


    @Override
    public Map<String, ValueLabelVo> buildHeader(String parentClassifyId) {
        Map<String, ValueLabelVo> map = new LinkedHashMap<>();
        List<ClassifyVO> byParentClassifyId = classifyService.getByParentClassifyId(parentClassifyId);
        //公共部分
        ValueLabelVo date = new ValueLabelVo();
        date.setLabel("日期");
        date.setValue("date");
        map.put("date", date);

        List<String> sum = new ArrayList<>();
        for (ClassifyVO classifyVO : byParentClassifyId) {
            ValueLabelVo bodyValueLabel = new ValueLabelVo();
            bodyValueLabel.setLabel(classifyVO.getClassifyName());
            bodyValueLabel.setValue(classifyVO.getClassifyId());
            List<ValueLabelVo> childrens = new ArrayList<>();

            ValueLabelVo children01 = new ValueLabelVo();
            children01.setLabel("剩余百分比");
            children01.setValue(classifyVO.getClassifyId() + "N01");
            //返回前端计算公式
            String rate = extractNumber(classifyVO.getClassifyName());
            children01.setRate(rate);
            children01.setTarget(classifyVO.getClassifyId() + "N02");
            childrens.add(children01);
            sum.add(classifyVO.getClassifyId() + "N02");

            ValueLabelVo children02 = new ValueLabelVo();
            children02.setLabel("剩余量/m³");
            children02.setValue(classifyVO.getClassifyId() + "N02");
            childrens.add(children02);

            bodyValueLabel.setChildren(childrens);
            map.put(classifyVO.getClassifyId(), bodyValueLabel);
        }


        ValueLabelVo totalSurplus = new ValueLabelVo();
        totalSurplus.setLabel("总剩余量/m³");
        totalSurplus.setValue("totalSurplus");
        totalSurplus.setSum(sum);
        map.put("totalSurplus", totalSurplus);

        ValueLabelVo dailyConsumption = new ValueLabelVo();
        dailyConsumption.setLabel("当日用量/m³");
        dailyConsumption.setValue("dailyConsumption");
        map.put("dailyConsumption", dailyConsumption);

        return map;
    }

    public String extractNumber(String input) {
        // 定义正则表达式，匹配"-"后面紧跟的数字，直到遇到"m"
        String regex = "-([\\d.]+)m";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(input);

        if (matcher.find()) {
            // 提取第一个捕获组的内容，即数字部分
            return matcher.group(1);
        } else {
            return null; // 如果没有匹配到，返回
        }
    }

    /**
     * 化学品存到day和history表中时会将每日总用量当作一个单独设备存储，其它分房间存储
     * @param exhaustGasChemicalsDto
     * @param userId
     */
    @Override
    @Transactional
    public void addOrUpdate(ExhaustGasChemicalsDto exhaustGasChemicalsDto, String userId) {
        LocalDateTime creteTime = LocalDateTime.now();
        LocalDateTime updateTime = creteTime;
        if (StringUtils.isNotEmpty(exhaustGasChemicalsDto.getId())) {
            //表示更新，先删除再更新
            ExhaustGasChemicalsData exhaustGasChemicalsData = exhausGasChemicalMapper.selectById(exhaustGasChemicalsDto.getId());
            creteTime = exhaustGasChemicalsData.getCreateTime();
            exhausGasChemicalMapper.deleteById(exhaustGasChemicalsDto.getId());

            LambdaQueryWrapper<ExhaustGasChemicalsRelData> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(ExhaustGasChemicalsRelData::getPid, exhaustGasChemicalsData.getId());
            exhausGasChemicalRelMapper.delete(wrapper);
        }
        List<ExhaustGasChemicalsData> chemicalsDataList = new ArrayList<>();
        List<ExhaustGasChemicalsRelData> chemicalsRelData = new ArrayList<>();
        List<ClassifyVO> byParentClassifyId = classifyService.getByParentClassifyId(exhaustGasChemicalsDto.getClassifyId());
        List<Map<String, String>> data = exhaustGasChemicalsDto.getData();
        List<MeasureDataDayBean> measureDataDayBeanList = new ArrayList<>();
        List<String> deviceCodes = new ArrayList<>();
        for (Map<String, String> datum : data) {
            //公共部分
            ExhaustGasChemicalsData exhaustGasChemicalsData = new ExhaustGasChemicalsData();
            MeasureDataDayBean measureDataDayBeanPublic = new MeasureDataDayBean();
            exhaustGasChemicalsData.setId(IdUtil.nextId());
            exhaustGasChemicalsData.setType(exhaustGasChemicalsDto.getClassifyId());
            exhaustGasChemicalsData.setRemark(datum.get("remark"));
            exhaustGasChemicalsData.setTotalSurplus(datum.get("totalSurplus"));
            exhaustGasChemicalsData.setDailyConsumption(datum.get("dailyConsumption"));
            if (!isValidDate(datum.get("date"))){
                throw new ConditionException("日期不正确");
            }
            exhaustGasChemicalsData.setDate(datum.get("date"));
            exhaustGasChemicalsData.setCreateTime(creteTime);
            exhaustGasChemicalsData.setUpdateTime(updateTime);
            exhaustGasChemicalsData.setCreateUserId(userId);
            exhaustGasChemicalsData.setUpdateUserId(userId);

            deviceCodes.add(exhaustGasChemicalsDto.getClassifyId());
            measureDataDayBeanPublic.setDeviceId(getDeviceId(exhaustGasChemicalsDto.getClassifyId()));
            measureDataDayBeanPublic.setMeasureValue(datum.get("totalSurplus"));
            // 化学品记录的时消耗量，系统内设备有的是增量，所以做个负数，配置图表时配置系数-1
            double temp = Double.parseDouble(datum.get("dailyConsumption"));
            measureDataDayBeanPublic.setIncrementValue(String.valueOf(temp*(-1)));
            // 获取前一天日期，填写的日期为填写日期，所以需要减一天
            measureDataDayBeanPublic.setMeasureDate(LocalDate.parse(datum.get("date")).minusDays(1));
            measureDataDayBeanPublic.setMeasureTime(LocalDateTime.parse(datum.get("date")+"T"+"00:00:00"));
            measureDataDayBeanList.add(measureDataDayBeanPublic);
            // 获取前一天的数据
            LocalDate yesterday = LocalDate.parse(datum.get("date")).minusDays(1);
            QueryWrapper<ExhaustGasChemicalsData> queryWrapper = new QueryWrapper<>();
            queryWrapper.lambda()
                    .eq(ExhaustGasChemicalsData::getType,exhaustGasChemicalsDto.getClassifyId())
                    .eq(ExhaustGasChemicalsData::getDate,yesterday.toString());
            ExhaustGasChemicalsData yesterdayData = exhausGasChemicalMapper.selectOne(queryWrapper);
            Map<String,String> yesterdayMap = new HashMap<>();
            if(ObjectUtil.isNotEmpty(yesterdayData)){
                QueryWrapper<ExhaustGasChemicalsRelData> queryWrapper1 = new QueryWrapper<>();
                queryWrapper1.lambda()
                        .eq(ExhaustGasChemicalsRelData::getPid,yesterdayData.getId());
                List<ExhaustGasChemicalsRelData> yesterdayDataList = exhausGasChemicalRelMapper.selectList(queryWrapper1);
                // 获取类型和剩余量的map
                yesterdayMap = yesterdayDataList.stream().collect(Collectors.toMap(ExhaustGasChemicalsRelData::getType,ExhaustGasChemicalsRelData::getSurplus));
            }
            for (ClassifyVO classifyVO : byParentClassifyId) {
                ExhaustGasChemicalsRelData exhaustGasChemicalsRelData = new ExhaustGasChemicalsRelData();
                MeasureDataDayBean measureDataDayBean = new MeasureDataDayBean();
                String classifyId = classifyVO.getClassifyId();
                String percent = datum.get(classifyId + "N01");
                String surplus = datum.get(classifyId + "N02");
                exhaustGasChemicalsRelData.setId(IdUtil.nextId());
                exhaustGasChemicalsRelData.setPid(exhaustGasChemicalsData.getId());
                exhaustGasChemicalsRelData.setType(classifyId);
                exhaustGasChemicalsRelData.setArea(classifyVO.getClassifyName());
                exhaustGasChemicalsRelData.setPercent(percent);
                exhaustGasChemicalsRelData.setSurplus(surplus);
                exhaustGasChemicalsRelData.setPid(exhaustGasChemicalsData.getId());
                exhaustGasChemicalsRelData.setCreateTime(creteTime);
                exhaustGasChemicalsRelData.setUpdateTime(updateTime);
                exhaustGasChemicalsRelData.setCreateUserId(userId);
                exhaustGasChemicalsRelData.setUpdateUserId(userId);
                chemicalsRelData.add(exhaustGasChemicalsRelData);

                // 分项单独算增量
                deviceCodes.add(classifyId);
                measureDataDayBean.setDeviceId(getDeviceId(classifyId));
                measureDataDayBean.setMeasureValue(surplus);
                measureDataDayBean.setMeasureDate(LocalDate.parse(datum.get("date")).minusDays(1));
                double surplusDouble = Double.parseDouble(surplus);
                double yesterdayDouble = Double.parseDouble(yesterdayMap.getOrDefault(classifyId,"0"));
                double increment = surplusDouble - yesterdayDouble;
                measureDataDayBean.setIncrementValue(String.valueOf(increment));
                measureDataDayBean.setMeasureTime(LocalDateTime.parse(datum.get("date")+"T"+"00:00:00"));
                measureDataDayBeanList.add(measureDataDayBean);
            }
            chemicalsDataList.add(exhaustGasChemicalsData);
        }
        exhausGasChemicalMapper.batchAdd(chemicalsDataList);
        exhausGasChemicalRelMapper.batchAdd(chemicalsRelData);

        List<MeasureDataDayBean> deviceInfoList = devicePropertyMapper.selectDeviceInfo(deviceCodes);
        saveMeasureData(measureDataDayBeanList,deviceInfoList);
    }

    @Override
    public Page<ExhaustGasChemicalsData> selectPage(ExhausGasChemicalQueryParam param) {
        List<Map<String, String>> res = new ArrayList<>();
        LambdaQueryWrapper<ExhaustGasChemicalsData> wrapper01 = new LambdaQueryWrapper<>();
        wrapper01.eq(ExhaustGasChemicalsData::getType, param.getClassifyId())
                .orderByDesc(ExhaustGasChemicalsData::getDate)
                .orderByDesc(ExhaustGasChemicalsData::getCreateTime);
        Page<ExhaustGasChemicalsData> page = new Page<>(param.getPageNo(), param.getPageSize());
        Page<ExhaustGasChemicalsData> exhaustGasChemicalsDataPage = exhausGasChemicalMapper.selectPage(page, wrapper01);
        exhaustGasChemicalsDataPage.convert(x -> {
            Map<String, String> map = new HashMap<>();
            //公共数据
            map.put("id", x.getId());
            map.put("date", x.getDate());
            map.put("totalSurplus", x.getTotalSurplus());
            map.put("dailyConsumption", x.getDailyConsumption());
            map.put("remark", x.getRemark());
            //填充数据
            LambdaQueryWrapper<ExhaustGasChemicalsRelData> wrapper02 = new LambdaQueryWrapper<>();
            wrapper02.eq(ExhaustGasChemicalsRelData::getPid, x.getId());
            List<ExhaustGasChemicalsRelData> list = exhausGasChemicalRelMapper.selectList(wrapper02);
            list.stream().forEach(y -> {
                String replace = y.getPercent().replace("%", "");
                map.put(y.getType() + "N01", replace);
                map.put(y.getType() + "N02", y.getSurplus());
                map.put("area", y.getArea());
            });
            return map;
        });
        return exhaustGasChemicalsDataPage;
    }

    @Override
    @Transactional
    public void importExcel(MultipartFile file, String userId) throws Exception {
        InputStream inputStream = file.getInputStream();
        Workbook workbook = new XSSFWorkbook(inputStream);
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            // 记录上一行的值
            Map<String,String> yesterdayRelMap = new HashMap<>();
            LocalDateTime now = LocalDateTime.now();
            List<ExhaustGasChemicalsData> chemicalsDataList = new ArrayList<>();
            List<ExhaustGasChemicalsRelData> chemicalsRelData = new ArrayList<>();
            // 同步数据到day和history表使用
            List<MeasureDataDayBean> measureDataDayBeanList = new ArrayList<>();
            Set<String> deviceCodes = new HashSet<>();
            Sheet sheet = workbook.getSheetAt(i);
            int lastRowNum = sheet.getLastRowNum();
            //先处理前两行表头
            Row row01 = sheet.getRow(0);
            Cell cell02 = row01.getCell(1);
            if (cell02 == null) {
                throw new ConditionException("请正确填写化学品名称");
            }
            String chemicalName = cell02.getStringCellValue();
            Classify classify = classifyService.detailByDescription("废气化学品_" + chemicalName);
            if (!Optional.ofNullable(classify).isPresent()) {
                throw new ConditionException("请正确填写化学品名称");
            }
            List<ClassifyVO> sortClassifyId = new ArrayList<>();
            List<ClassifyVO> byParentClassifyId = classifyService.getByParentClassifyId(classify.getClassifyId());
            //排下序，得跟导入的表格第二行顺序一致
            Row row02 = sheet.getRow(1);
            int row02CellNum = row02.getLastCellNum();
            for (int j = 1; j < row02CellNum - 3; j++) {
                for (ClassifyVO classifyVO : byParentClassifyId) {
                    if (classifyVO.getClassifyName().equals(row02.getCell(j).getStringCellValue())) {
                        sortClassifyId.add(classifyVO);
                    }
                }
            }
            List<String> date  = new ArrayList<>();
            //处理数据
            for (int j = 3; j <= lastRowNum; j++) {
                // 保存当前行的值
                List<ExhaustGasChemicalsRelData> yesterdayRelList = new ArrayList<>();
                //处理公共数据
                ExhaustGasChemicalsData exhaustGasChemicalsData = new ExhaustGasChemicalsData();
                MeasureDataDayBean measureDataDayBeanPublic = new MeasureDataDayBean();
                exhaustGasChemicalsData.setId(IdUtil.nextId());
                exhaustGasChemicalsData.setType(classify.getClassifyId());
                exhaustGasChemicalsData.setCreateTime(now);
                exhaustGasChemicalsData.setUpdateTime(now);
                exhaustGasChemicalsData.setCreateUserId(userId);
                exhaustGasChemicalsData.setUpdateUserId(userId);

                deviceCodes.add(classify.getClassifyId());
                measureDataDayBeanPublic.setDeviceId(getDeviceId(classify.getClassifyId()));
                LocalDate yesterday = LocalDate.now();
                Row row = sheet.getRow(j);
                short lastCellNum = row.getLastCellNum();
                for (int k = 0; k < lastCellNum; k++) {
                    Cell cell = row.getCell(k);
                    String cellValue = null;
                    if (k == 0) {
                        cellValue = getDateCellValue(cell);
                        exhaustGasChemicalsData.setDate(cellValue);
                        date.add(cellValue);
                        yesterday = LocalDate.parse(cellValue).minusDays(2);
                        measureDataDayBeanPublic.setMeasureDate(LocalDate.parse(cellValue).minusDays(1));
                    } else if (k == lastCellNum - 3) {
                        cellValue = getCellValue(cell);
                        if(StrUtil.isBlank(cellValue)){
                            throw new ConditionException("总剩余量为空");
                        }
                        exhaustGasChemicalsData.setTotalSurplus(cellValue);
                        measureDataDayBeanPublic.setMeasureValue(cellValue);
                    } else if (k == lastCellNum - 2) {
                        cellValue = getCellValue(cell);
                        String increment = new String();
                        if(StrUtil.isBlank(cellValue)){
                            throw new ConditionException("当日用量为空");
                        }
                        if("\\".equals(cellValue) || "/".equals(cellValue)){
                            cellValue = "0";
                            increment = "0";
                        }else{
                            double temp = 0.0;
                            if(cellValue.contains("%")){
                                cellValue = cellValue.replace("%","");
                                temp = Double.parseDouble(cellValue)/100;
                            }else{
                                temp = Double.parseDouble(cellValue);
                            }
                            increment = String.valueOf(temp*(-1));
                            cellValue = String.valueOf(temp);
                        }
                        exhaustGasChemicalsData.setDailyConsumption(cellValue);
                        measureDataDayBeanPublic.setIncrementValue(increment);
                    } else if (k == lastCellNum - 1) {
                        cellValue = getCellValue(cell);
                        exhaustGasChemicalsData.setRemark(cellValue);
                    }
                }
                chemicalsDataList.add(exhaustGasChemicalsData);
                measureDataDayBeanPublic.setMeasureTime(LocalDateTime.parse(measureDataDayBeanPublic.getMeasureDate().plusDays(1)+"T"+"00:00:00"));
                measureDataDayBeanList.add(measureDataDayBeanPublic);
                // 判断yesterdayRelMap是否为空，为空说明是首行，需要去库里查数据
                if(yesterdayRelMap.isEmpty()){
                    // 获取前一天的数据
                    QueryWrapper<ExhaustGasChemicalsData> queryWrapper = new QueryWrapper<>();
                    queryWrapper.lambda()
                            .eq(ExhaustGasChemicalsData::getType,classify.getClassifyId())
                            .eq(ExhaustGasChemicalsData::getDate,yesterday.toString());
                    ExhaustGasChemicalsData yesterdayData = exhausGasChemicalMapper.selectOne(queryWrapper);
                    if(ObjectUtil.isNotEmpty(yesterdayData)){
                        QueryWrapper<ExhaustGasChemicalsRelData> queryWrapper1 = new QueryWrapper<>();
                        queryWrapper1.lambda()
                                .eq(ExhaustGasChemicalsRelData::getPid,yesterdayData.getId());
                        List<ExhaustGasChemicalsRelData> yesterdayDataList = exhausGasChemicalRelMapper.selectList(queryWrapper1);
                        // 获取类型和剩余量的map
                        yesterdayRelMap = yesterdayDataList.stream().collect(Collectors.toMap(ExhaustGasChemicalsRelData::getType,ExhaustGasChemicalsRelData::getSurplus));
                    }
                }

                //有sortClassifyId.size()组数据
                int flag = 1;
                for (ClassifyVO classifyVO : sortClassifyId) {
                    ExhaustGasChemicalsRelData exhaustGasChemicalsRelData = new ExhaustGasChemicalsRelData();
                    MeasureDataDayBean measureDataDayBean = new MeasureDataDayBean();
                    Cell cellA = row.getCell(2 * flag - 1);
                    String percent = getCellValue(cellA);
                    Cell cellB = row.getCell(2 * flag);
                    String surplus = getCellValue(cellB);
                    exhaustGasChemicalsRelData.setArea(classifyVO.getClassifyName());
                    exhaustGasChemicalsRelData.setType(classifyVO.getClassifyId());
                    exhaustGasChemicalsRelData.setPid(exhaustGasChemicalsData.getId());
                    exhaustGasChemicalsRelData.setPercent(percent);
                    exhaustGasChemicalsRelData.setSurplus(surplus);
                    exhaustGasChemicalsRelData.setCreateTime(now);
                    exhaustGasChemicalsRelData.setUpdateTime(now);
                    exhaustGasChemicalsRelData.setCreateUserId(userId);
                    exhaustGasChemicalsRelData.setUpdateUserId(userId);
                    if(ObjectUtil.isEmpty(exhaustGasChemicalsRelData) || StrUtil.isBlank(exhaustGasChemicalsRelData.getSurplus())) {
                        throw new ConditionException("剩余量为空");
                    }
                    if (ObjectUtil.isNotEmpty(exhaustGasChemicalsData) && StrUtil.isNotBlank(exhaustGasChemicalsData.getDate())){
                        deviceCodes.add(classifyVO.getClassifyId());
                        measureDataDayBean.setDeviceId(getDeviceId(classifyVO.getClassifyId()));
                        measureDataDayBean.setMeasureValue(surplus);
                        measureDataDayBean.setMeasureDate(LocalDate.parse(exhaustGasChemicalsData.getDate()));
                        measureDataDayBean.setMeasureTime(LocalDateTime.parse(exhaustGasChemicalsData.getDate()+"T"+"00:00:00"));
                        double surplusDouble = Double.parseDouble(surplus);
                        double yesterdayDouble = Double.parseDouble(yesterdayRelMap.getOrDefault(classifyVO.getClassifyId(),"0"));
                        double increment = surplusDouble - yesterdayDouble;
                        measureDataDayBean.setIncrementValue(String.valueOf(increment));
                        measureDataDayBeanList.add(measureDataDayBean);
                    }
                    chemicalsRelData.add(exhaustGasChemicalsRelData);
                    yesterdayRelList.add(exhaustGasChemicalsRelData);
                    flag++;
                }
                // 记录上一行的值
                if(CollectionUtil.isNotEmpty(yesterdayRelList)){
                    // 获取类型和剩余量的map
                    yesterdayRelMap = yesterdayRelList.stream().collect(Collectors.toMap(ExhaustGasChemicalsRelData::getType,ExhaustGasChemicalsRelData::getSurplus));
                }
            }
            LambdaQueryWrapper<ExhaustGasChemicalsData> wrapper01 = new LambdaQueryWrapper<>();
            wrapper01.select(ExhaustGasChemicalsData::getId)
                    .eq(ExhaustGasChemicalsData::getType, classify.getClassifyId())
                    .in(ExhaustGasChemicalsData::getDate, date);
            List<String> repetitionId = exhausGasChemicalMapper.selectList(wrapper01).stream()
                    .map(ExhaustGasChemicalsData::getId).collect(Collectors.toList());

            if (!CollectionUtils.isEmpty(repetitionId)){
                //重复数据需要删除
                exhausGasChemicalMapper.batchDeletedById(repetitionId);
                exhausGasChemicalRelMapper.batchDeletedByPid(repetitionId);
            }

            if (!CollectionUtils.isEmpty(chemicalsDataList) && !CollectionUtils.isEmpty(chemicalsRelData)){
                exhausGasChemicalMapper.batchAdd(chemicalsDataList);
                exhausGasChemicalRelMapper.batchAdd(chemicalsRelData);
            }

            if(!deviceCodes.isEmpty()){
                List<String> deviceCodeList = deviceCodes.stream().toList();
                List<MeasureDataDayBean> deviceInfoList = devicePropertyMapper.selectDeviceInfo(deviceCodeList);
                if(deviceInfoList.isEmpty()){
                    throw new ConditionException("设备信息为空,未在系统中配置采集设备");
                }
                saveMeasureData(measureDataDayBeanList,deviceInfoList);
            }
        }
    }

//    @Override
//    public void exportTemplate(HttpServletResponse response) {
//        Workbook wb = new XSSFWorkbook();
//        Sheet sheet = wb.createSheet("氢氧化钠");
//        sheet.setColumnWidth(0, 15 * 256);
//        sheet.setColumnWidth(1, 12 * 256);
//        sheet.setColumnWidth(2, 12 * 256);
//        sheet.setColumnWidth(3, 12 * 256);
//        sheet.setColumnWidth(4, 12 * 256);
//        sheet.setColumnWidth(5, 12 * 256);
//        sheet.setColumnWidth(6, 12 * 256);
//        sheet.setColumnWidth(7, 12 * 256);
//
//        CellStyle style = wb.createCellStyle();
//        Font font = wb.createFont();
//        font.setColor(IndexedColors.BLACK.getIndex());
//        font.setFontHeightInPoints((short) 10);
//        font.setFontName("宋体");
//        style.setFont(font);
//        style.setAlignment(HorizontalAlignment.CENTER);// 设置水平居中
//        style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
//        style.setWrapText(true);  // 换行
//        // 设置边框样式
//        style.setBorderTop(BorderStyle.THIN); // 上边框
//        style.setBorderBottom(BorderStyle.THIN); // 下边框
//        style.setBorderLeft(BorderStyle.THIN); // 左边框
//        style.setBorderRight(BorderStyle.THIN); // 右边框
//
//        // 设置边框颜色
//        style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框颜色
//        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框颜色
//        style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框颜色
//        style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框颜色
//
//
//        //第一行
//        Row row0 = sheet.createRow(0);
//        Cell cell00 = row0.createCell(0);
//        cell00.setCellValue("化学品");
//        cell00.setCellStyle(style);
//        Cell cell01 = row0.createCell(1);
//        cell01.setCellValue("氢氧化钠");
//        cell01.setCellStyle(style);
//        CellRangeAddress regionRow0 = new CellRangeAddress(0, 0, 1, 6);
//        sheet.addMergedRegion(regionRow0);
//
//        Cell cell07 = row0.createCell(7);
//        cell07.setCellValue(" ");
//        cell07.setCellStyle(style);
//        //第二、三行
//        Row row01 = sheet.createRow(1);
//        Cell cell010 = row01.createCell(0);
//        cell010.setCellValue("日期");
//        cell010.setCellStyle(style);
//        Cell cell011 = row01.createCell(1);
//        cell011.setCellValue("1#楼加药间-20m³");
//        cell011.setCellStyle(style);
//        Cell cell013 = row01.createCell(3);
//        cell013.setCellValue("1#楼屋面-10m³");
//        cell013.setCellStyle(style);
//        Cell cell014 = row01.createCell(5);
//        cell014.setCellValue("总剩余量/m³");
//        cell014.setCellStyle(style);
//        Cell cell015 = row01.createCell(6);
//        cell015.setCellValue("当日用量/m³");
//        cell015.setCellStyle(style);
//        Cell cell016 = row01.createCell(7);
//        cell016.setCellValue("备注");
//        cell016.setCellStyle(style);
//
//        Row row02 = sheet.createRow(2);
//        Cell cell021 = row02.createCell(1);
//        cell021.setCellValue("剩余百分比");
//        cell021.setCellStyle(style);
//        Cell cell022 = row02.createCell(2);
//        cell022.setCellValue("剩余量/m³");
//        cell022.setCellStyle(style);
//        Cell cell023 = row02.createCell(3);
//        cell023.setCellValue("剩余百分比");
//        cell023.setCellStyle(style);
//        Cell cell024 = row02.createCell(4);
//        cell024.setCellValue("剩余量/m³");
//        cell024.setCellStyle(style);
//        Cell cell027 = row02.createCell(7);
//        cell027.setCellStyle(style);
//
//        CellRangeAddress regionRow1Row2Cell0 = new CellRangeAddress(1, 2, 0, 0);
//        sheet.addMergedRegion(regionRow1Row2Cell0);
//
//
//        CellRangeAddress regionRow1Cell1 = new CellRangeAddress(1, 1, 1, 2);
//        sheet.addMergedRegion(regionRow1Cell1);
//
//
//        CellRangeAddress regionRow1Cell2 = new CellRangeAddress(1, 1, 3, 4);
//        sheet.addMergedRegion(regionRow1Cell2);
//
//
//        CellRangeAddress regionRow1Row2Cell1 = new CellRangeAddress(1, 2, 5, 5);
//        sheet.addMergedRegion(regionRow1Row2Cell1);
//
//
//        CellRangeAddress regionRow1Row2Cell2 = new CellRangeAddress(1, 2, 6, 6);
//        sheet.addMergedRegion(regionRow1Row2Cell2);
//
//        //第四行
//        // 获取创建帮助器
//
//        CreationHelper createHelper = wb.getCreationHelper();
//        // 获取DataFormat对象
//        DataFormat format = wb.createDataFormat();
//        // 创建一个单元格样式并设置日期格式，例如："yyyy-mm-dd"
//        short dateFormat = format.getFormat("yyyy-mm-dd");
//        CellStyle styleDate = wb.createCellStyle();
//        styleDate.setDataFormat(dateFormat);
//        // 设置边框样式
//        styleDate.setBorderTop(BorderStyle.THIN); // 上边框
//        styleDate.setBorderBottom(BorderStyle.THIN); // 下边框
//        styleDate.setBorderLeft(BorderStyle.THIN); // 左边框
//        styleDate.setBorderRight(BorderStyle.THIN); // 右边框
//
//        // 设置边框颜色
//        styleDate.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框颜色
//        styleDate.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框颜色
//        styleDate.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框颜色
//        styleDate.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框颜色
//
//        styleDate.setFont(font);
//        styleDate.setAlignment(HorizontalAlignment.CENTER);// 设置水平居中
//        styleDate.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
//        styleDate.setWrapText(true);  // 换行
//
//        Row row03 = sheet.createRow(3);
//        Cell cell30 = row03.createCell(0);
//        cell30.setCellValue(LocalDate.now());
//        cell30.setCellStyle(styleDate);
//
//        Cell cell31 = row03.createCell(1);
//        cell31.setCellValue("46.60%");
//        cell31.setCellStyle(style);
//
//        Cell cell32 = row03.createCell(2);
//        cell32.setCellValue("4.66");
//        cell32.setCellFormula("B4*10");
//        cell32.setCellStyle(style);
//
//        Cell cell33 = row03.createCell(3);
//        cell33.setCellValue("12.20%");
//        cell33.setCellStyle(style);
//
//        Cell cell34 = row03.createCell(4);
//        cell34.setCellValue("1.22");
//        cell34.setCellStyle(style);
//        cell34.setCellFormula("D4*10");
//
//        Cell cell35 = row03.createCell(5);
//        cell35.setCellStyle(style);
//        cell35.setCellFormula("C4+E4");
//
//        Cell cell36 = row03.createCell(6);
//        cell36.setCellValue("/");
//        cell36.setCellStyle(style);
//
//        Cell cell37 = row03.createCell(7);
//        cell37.setCellStyle(style);
//
//        //第五行
//        Row row04 = sheet.createRow(4);
//        Cell cell40 = row04.createCell(0);
//        cell40.setCellValue(LocalDate.now().plusDays(1));
//        cell40.setCellStyle(styleDate);
//
//        Cell cell41 = row04.createCell(1);
//        cell41.setCellValue("11.10%");
//        cell41.setCellStyle(style);
//
//        Cell cell42 = row04.createCell(2);
//        cell42.setCellValue("1.11");
//        cell42.setCellStyle(style);
//        cell42.setCellFormula("B5*10");
//
//        Cell cell43 = row04.createCell(3);
//        cell43.setCellValue("42.80%");
//        cell43.setCellStyle(style);
//
//        Cell cell44 = row04.createCell(4);
//        cell44.setCellValue("4.82");
//        cell44.setCellStyle(style);
//        cell44.setCellFormula("D5*10");
//
//        Cell cell45 = row04.createCell(5);
//        cell45.setCellValue("5.39");
//        cell45.setCellStyle(style);
//        cell45.setCellFormula("C5+E5");
//
//        Cell cell46 = row04.createCell(6);
//        cell46.setCellValue("0.49");
//        cell46.setCellStyle(style);
//        cell46.setCellFormula("F4-F5");
//
//        Cell cell47 = row04.createCell(7);
//        cell47.setCellValue("药液充装");
//        cell47.setCellStyle(style);
//
//        initRegionCellList(sheet.getMergedRegions(), sheet);
//
//        try {
//            OutputStream out = response.getOutputStream();
//            wb.write(out);
//            out.flush();
//            out.close();
//        } catch (Exception e) {
//            throw new ConditionException(e.getMessage());
//        }
//    }

    public void exportTemplate(HttpServletResponse response) {
        Workbook wb = new XSSFWorkbook();
        CellStyle style = styleExcel(wb);
        CellStyle styleDate = styleDateExcel(wb);
        List<Classify> exhausGasClassifys = classifyService.classifysByDescription("废气化学品_");
        for (Classify classify : exhausGasClassifys) {
            Sheet sheet = wb.createSheet(classify.getClassifyName());
            List<ClassifyVO> childs = classifyService.getByParentClassifyId(classify.getClassifyId());
            int colSum = childs.size()*2 + 4;
            for (int i = 0; i < colSum; i++) {
                if (i == 0){
                    sheet.setColumnWidth(i, 15 * 256);
                }else {
                    sheet.setColumnWidth(i, 12 * 256);
                }
            }
            //第一行
            Row row0 = sheet.createRow(0);
            Cell cell00 = row0.createCell(0);
            cell00.setCellValue("化学品");
            cell00.setCellStyle(style);
            Cell cell01 = row0.createCell(1);
            cell01.setCellValue(classify.getClassifyName());
            cell01.setCellStyle(style);
            CellRangeAddress regionRow0 = new CellRangeAddress(0, 0, 1, colSum-2);
            sheet.addMergedRegion(regionRow0);
            Cell cell07 = row0.createCell(7);
            cell07.setCellValue(" ");
            cell07.setCellStyle(style);

            //第二、三行
            Row row01 = sheet.createRow(1);
            Cell cell010 = row01.createCell(0);
            cell010.setCellValue("日期");
            cell010.setCellStyle(style);
            for (int i = 0; i < childs.size(); i++) {
                Cell cell = row01.createCell(2*i+1);
                cell.setCellValue(childs.get(i).getClassifyName());
                cell.setCellStyle(style);
            }
            Cell totalSurplusCell = row01.createCell(childs.size()*2+1);
            totalSurplusCell.setCellValue("总剩余量/m³");
            totalSurplusCell.setCellStyle(style);
            Cell dailyConsumptionCell = row01.createCell(childs.size()*2+2);
            dailyConsumptionCell.setCellValue("当日用量/m³");
            dailyConsumptionCell.setCellStyle(style);
            Cell remarkCell = row01.createCell(childs.size()*2+3);
            remarkCell.setCellValue("备注");
            remarkCell.setCellStyle(style);

            Row row02 = sheet.createRow(2);
            for (int i = 0; i < childs.size(); i++) {
                Cell percentCell = row02.createCell(2*i+1);
                percentCell.setCellValue("剩余百分比");
                percentCell.setCellStyle(style);
                Cell surplusCell = row02.createCell(2*i+2);
                surplusCell.setCellValue("剩余量/m³");
                surplusCell.setCellStyle(style);
            }
            Cell cell02End = row02.createCell(colSum-1);
            cell02End.setCellStyle(style);

            CellRangeAddress regionRow1Row2Cell0 = new CellRangeAddress(1, 2, 0, 0);
            sheet.addMergedRegion(regionRow1Row2Cell0);

            for (int i = 0; i < childs.size(); i++) {
                CellRangeAddress regionRow1Cell = new CellRangeAddress(1, 1, 2*i+1, 2*i+2);
                sheet.addMergedRegion(regionRow1Cell);
            }
            CellRangeAddress regionRow12CellSurplus = new CellRangeAddress(1, 2, colSum-3, colSum-3);
            sheet.addMergedRegion(regionRow12CellSurplus);

            CellRangeAddress regionRow12CellDailyConsumption	 = new CellRangeAddress(1, 2, colSum-2, colSum-2);
            sheet.addMergedRegion(regionRow12CellDailyConsumption);

            //第四行
            Row row03 = sheet.createRow(3);
            Cell cell30 = row03.createCell(0);
            cell30.setCellValue(LocalDate.now().minusDays(1));
            cell30.setCellStyle(styleDate);

            for (int i = 0; i < childs.size(); i++) {
                String rate = extractNumber(childs.get(i).getClassifyName());
                Cell cell03Percent = row03.createCell(2*i+1);
                cell03Percent.setCellValue("46.60%");
                cell03Percent.setCellStyle(style);

                String character = getCharacter(i,'B',2);
                Cell cell03Surplus = row03.createCell(2*i+2);
                cell03Surplus.setCellFormula(character+"4*"+rate);
                cell03Surplus.setCellStyle(style);
            }

            Cell cell3Surplus = row03.createCell(colSum-3);
            cell3Surplus.setCellStyle(style);
            String sumRow3FormulaString = "";
            for (int i = 0; i < childs.size(); i++) {
                String character = getCharacter(i,'C',2);
                sumRow3FormulaString += character+"4+";
            }
            cell3Surplus.setCellFormula(sumRow3FormulaString.substring(0,sumRow3FormulaString.length()-1));

            Cell cell3DailyConsumption	 = row03.createCell(colSum-2);
            cell3DailyConsumption.setCellValue("0");
            cell3DailyConsumption.setCellStyle(style);

            Cell cell3End = row03.createCell(colSum-1);
            cell3End.setCellStyle(style);

            //第五行
            Row row04 = sheet.createRow(4);
            Cell cell40 = row04.createCell(0);
            cell40.setCellValue(LocalDate.now());
            cell40.setCellStyle(styleDate);


            for (int i = 0; i < childs.size(); i++) {
                String rate = extractNumber(childs.get(i).getClassifyName());
                Cell cell04Percent = row04.createCell(2*i+1);
                cell04Percent.setCellValue("52.10%");
                cell04Percent.setCellStyle(style);

                String character = getCharacter(i,'B',2);
                Cell cell04Surplus = row04.createCell(2*i+2);
                cell04Surplus.setCellFormula(character+"5*"+rate);
                cell04Surplus.setCellStyle(style);
            }

            Cell cell4Surplus = row04.createCell(colSum-3);
            cell4Surplus.setCellStyle(style);
            String sumRow4FormulaString = "";
            for (int i = 0; i < childs.size(); i++) {
                String character = getCharacter(i,'C',2);
                sumRow4FormulaString += character+"5+";
            }
            cell4Surplus.setCellFormula(sumRow4FormulaString.substring(0,sumRow4FormulaString.length()-1));

            Cell cell4DailyConsumption	 = row04.createCell(colSum-2);
            cell4DailyConsumption.setCellStyle(style);
            String character = getCharacter(childs.size()-1,'D',2);
            cell4DailyConsumption.setCellFormula(character+"4-"+character+"5");

            Cell cell4End = row04.createCell(colSum-1);
            cell4End.setCellValue("药液充装");
            cell4End.setCellStyle(style);

            initRegionCellList(sheet.getMergedRegions(), sheet);
        }

        try {
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            throw new ConditionException(e.getMessage());
        }
    }

//    public String getCharacter(int i,char startChar) {
//        // 'B' 的 ASCII 码是 66，每增加一个索引，ASCII 码增加 2
//        // 返回值类型为 String
//        return String.valueOf((char) (startChar + i * 2));
//    }

    public String getCharacter(int i,char startChar,int rate) {
        // 'B' 的 ASCII 码是 66，每增加一个索引，ASCII 码增加 rate
        // 返回值类型为 String
        return String.valueOf((char) (startChar + i * rate));
    }

    public CellStyle styleExcel(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        Font font = getFont(wb);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);// 设置水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        style.setWrapText(true);  // 换行
        // 设置边框样式
        style.setBorderTop(BorderStyle.THIN); // 上边框
        style.setBorderBottom(BorderStyle.THIN); // 下边框
        style.setBorderLeft(BorderStyle.THIN); // 左边框
        style.setBorderRight(BorderStyle.THIN); // 右边框

        // 设置边框颜色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框颜色
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框颜色
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框颜色
        style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框颜色
        return style;
    }

    public Font getFont(Workbook wb){
        Font font = wb.createFont();
        font.setColor(IndexedColors.BLACK.getIndex());
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");
        return font;
    }

    public CellStyle styleDateExcel(Workbook wb){
        // 获取创建帮助器
        CreationHelper createHelper = wb.getCreationHelper();
        // 获取DataFormat对象
        DataFormat format = wb.createDataFormat();
        // 创建一个单元格样式并设置日期格式，例如："yyyy-mm-dd"
        short dateFormat = format.getFormat("yyyy-mm-dd");
        CellStyle styleDate = wb.createCellStyle();
        styleDate.setDataFormat(dateFormat);
        // 设置边框样式
        styleDate.setBorderTop(BorderStyle.THIN); // 上边框
        styleDate.setBorderBottom(BorderStyle.THIN); // 下边框
        styleDate.setBorderLeft(BorderStyle.THIN); // 左边框
        styleDate.setBorderRight(BorderStyle.THIN); // 右边框

        // 设置边框颜色
        styleDate.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框颜色
        styleDate.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框颜色
        styleDate.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框颜色
        styleDate.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框颜色

        Font font = getFont(wb);
        styleDate.setFont(font);
        styleDate.setAlignment(HorizontalAlignment.CENTER);// 设置水平居中
        styleDate.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        styleDate.setWrapText(true);  // 换行
        return styleDate;
    }

    /**
     * 为合并的单元格设置样式（可根据需要自行调整）
     */
    @SuppressWarnings("deprecation")
    public static void setRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            Row row = sheet.getRow(i);
            if (null == row) row = sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                Cell cell = row.getCell(j);
                if (null == cell) cell = row.createCell(j);
                cell.setCellStyle(cs);
            }
        }
    }

    /**
     * 带边框的样式+
     */
    public static CellStyle setDefaultStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        // 边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        return cellStyle;
    }


    public void initRegionCellList(List<CellRangeAddress> regions, Sheet sheet) {
        for (CellRangeAddress region : regions) {
            // 使用RegionUtil类为合并后的单元格添加边框
            RegionUtil.setBorderBottom(BorderStyle.valueOf((short) 1), region, sheet); // 下边框
            RegionUtil.setBorderLeft(BorderStyle.valueOf((short) 1), region, sheet); // 左边框
            RegionUtil.setBorderRight(BorderStyle.valueOf((short) 1), region, sheet); // 有边框
            RegionUtil.setBorderTop(BorderStyle.valueOf((short) 1), region, sheet); // 上边框
            RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), region, sheet); // 下边框
            RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), region, sheet); // 左边框
            RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), region, sheet); // 有边框
            RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), region, sheet); // 上边框
        }
    }

    public List<ClassifyVO> getNeedClassifyId(List<ClassifyVO> byParentClassifyId) {
        List<ClassifyVO> res = new ArrayList<>();
        ClassifyVO classifyVOItem = new ClassifyVO();
        for (ClassifyVO classifyVO : byParentClassifyId) {
            BeanUtils.copyProperties(classifyVO, classifyVOItem);
            classifyVOItem.setRootClassifyId(classifyVO.getClassifyId() + "N01");
            res.add(classifyVOItem);
            classifyVOItem.setRootClassifyId(classifyVO.getClassifyId() + "N02");
            res.add(classifyVOItem);
        }
        return res;
    }

    private static String getCellValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return "";
        }
        switch (cell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat sdf = new SimpleDateFormat("MM/dd");
                    String formattedDate = sdf.format(date);
                    return formattedDate;
                } else {
                    double numericValue = cell.getNumericCellValue();
                    DecimalFormat decimalFormat = new DecimalFormat("0.00%");
                    return decimalFormat.format(numericValue);
                }
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case BLANK:
                return "";
            case FORMULA:
                switch (cell.getCachedFormulaResultType()) {
                    case NUMERIC:
                        // 创建DecimalFormat对象，设置格式为保留3位小数
                        DecimalFormat decimalFormat = new DecimalFormat("0.000");
                        // 格式化数字
                        String formattedNumber = decimalFormat.format(cell.getNumericCellValue());
                        // 去掉末尾的0
                        formattedNumber = formattedNumber.replaceAll("0*$", "");
                        // 如果小数点后没有数字，则去掉小数点
                        formattedNumber = formattedNumber.replaceAll("\\.$", "");
                        return formattedNumber;
                    case STRING:
                        return cell.getStringCellValue();
                    case BOOLEAN:
                        return String.valueOf(cell.getBooleanCellValue());
                    case ERROR:
                        return "错误公式";
                    default:
                        return "";
                }
            default:
                return "error";
        }
    }

    //处理日期类型
    private static String getDateCellValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return "";
        }
        Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String formattedDate = sdf.format(date);
        if (!isValidDate(formattedDate)){
            throw new ConditionException("日期不正确");
        }
        return StringUtils.isEmpty(formattedDate) ? null : formattedDate;

    }

    // 验证日期是否正确
    public static boolean isValidDate(String dateStr) {
        try {
            if (dateStr.isBlank()){
                return false;
            }
            // 定义日期格式
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            // 尝试解析日期字符串
            LocalDate.parse(dateStr, formatter);
            return true;
        } catch (DateTimeParseException e) {
            // 若解析过程中抛出异常，说明日期不合法
            return false;
        }
    }

    @Override
    @Transactional
    public void delete(String id) {
        // 根据id获得设备的date
        ExhaustGasChemicalsData exhaustGasChemicalsData = exhausGasChemicalMapper.selectById(id);
        // 获取设备code
        QueryWrapper<ExhaustGasChemicalsRelData> exhaustGasChemicalsRelDataQueryWrapperQueryWrapper = new QueryWrapper<>();
        exhaustGasChemicalsRelDataQueryWrapperQueryWrapper.lambda()
                .eq(ExhaustGasChemicalsRelData::getPid,id);
        List<String> deviceCodes = exhausGasChemicalRelMapper.selectList(exhaustGasChemicalsRelDataQueryWrapperQueryWrapper).stream()
                .map(ExhaustGasChemicalsRelData::getType).toList();
        // 获取设备id
        List<String> deviceIds = deviceMapper.batchGetDeviceId(deviceCodes);

        deviceMeasureDataDayMapper.batchDeleteByIdDate(deviceIds,exhaustGasChemicalsData.getDate());
        deviceMeasureDataHistoryMapper.batchDeleteByIdDate(deviceIds,exhaustGasChemicalsData.getDate());

        exhausGasChemicalMapper.deleteById(id);
        LambdaQueryWrapper<ExhaustGasChemicalsRelData> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(ExhaustGasChemicalsRelData::getPid, id);
        exhausGasChemicalRelMapper.delete(wrapper);
    }

    // 根据设备Code获得设备id
    private String getDeviceId(String deviceCode){
        QueryWrapper<DeviceEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda()
                .eq(StrUtil.isNotBlank(deviceCode),DeviceEntity::getCode,deviceCode);
        DeviceEntity deviceEntity = deviceMapper.selectOne(queryWrapper);
        return deviceEntity.getId();
    }

    // 保存更新的值到day表和history表中
    private void saveMeasureData(List<MeasureDataDayBean> measureDataDayBeanList,List<MeasureDataDayBean> deviceInfoList) {
        Map<String,MeasureDataDayBean> measureDataMap = deviceInfoList.stream().collect(Collectors.toMap(MeasureDataDayBean::getDeviceId, x -> x));
        measureDataDayBeanList.stream().forEach(x -> {
            MeasureDataDayBean measureDataDayBean = measureDataMap.get(x.getDeviceId());
            if (measureDataDayBean != null){
                x.setDataTypeId(measureDataDayBean.getDataTypeId());
                x.setUnitId(measureDataDayBean.getUnitId());
                setDayPrice(x.getMeasureTime(),x.getDataTypeId(),x);
            }
        });
        // 存储到表中
        measureDataDayStorageService.storageMeasureDataDayList(measureDataDayBeanList);
    }
    /**
     * 设置天级别的费用，根据dataType值进行设置费用，因为化学品是日用量，增量是负数，所以需要乘以-1
     * @param date
     * @param dataTypeId
     * @param dayBean
     */
    private void setDayPrice(LocalDateTime date, String dataTypeId, MeasureDataDayBean dayBean) {
        try {
            BigDecimal incrementValue = new BigDecimal(dayBean.getIncrementValue());
            BigDecimal price = energyPriceService.getEnergyPriceByDataTypeId(dataTypeId, date);
            // incrementValue乘-1
            incrementValue = incrementValue.multiply(BigDecimal.valueOf(-1));
            if (price != null) {
                dayBean.setIncrementCost(incrementValue.multiply(price));
            }
        }catch (NumberFormatException e){
            throw new ConditionException("数值格式不正确，有不正确的数据格式");
        }

    }

}
